/**
 * 
 */
package net.kazed.nextaction.database;

import java.util.HashMap;
import java.util.Map;

import net.kazed.nextaction.database.GtdContext.Column;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String TABLE_TASK = "task";
    public static final String TABLE_TASK_CONTACT = "task_contact";
    public static final String TABLE_TASK_ATTACHMENT = "task_attachment";
    public static final String TABLE_CONTEXT = "context";
    
    private static final String TAG = "DatabaseHelper";
    private static final int DATABASE_VERSION = 19;
    
    public static final Map<String, String> CONTEXT_LIST_PROJECT_MAP;
    public static final Map<String, String> TASK_LIST_PROJECT_MAP;
    public static final Map<String, String> TASK_ATTACHMENT_PROJECT_MAP;

        /* load task along with its optional project and context all in one query */
    //    static final String cTaskJoinTableNames = "task left outer join project on task.projectId = project._id " +
    //    		                                       "left outer join context on task.contextId = context._id";
    
    /**
     * Constructor.
     * @param context Android context.
     */
    public DatabaseHelper(Context context) {
        super(context, GtdProvider.DATABASE_NAME, null, DatabaseHelper.DATABASE_VERSION);
    }
	
    @Override
    public void onCreate(SQLiteDatabase db) {
    	Log.i(TAG, "Creating nextaction DB");
        db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_CONTEXT + " ("
        		+ "_id INTEGER PRIMARY KEY,"
                + "name TEXT," 
                + "parentContextId INTEGER,"
                + GtdContext.Column.NUMBER_OF_TASKS + " INTEGER,"
                + "colour INTEGER," 
                + "iconId INTEGER" + ");");

        db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_TASK + " (" 
        		+ "_id INTEGER PRIMARY KEY,"
                + "description TEXT," 
                + "details TEXT," 
                + "contextId INTEGER,"
                + "parentTaskId INTEGER,"
                + "created INTEGER,"
                + "modified INTEGER,"
                + "startDate INTEGER,"
                + "dueDate INTEGER,"
                + "displayOrder INTEGER,"
                + "complete INTEGER, "
                + Task.Column.TYPE + " INTEGER, "
                + Task.Column.STATUS + " INTEGER, "
                + Task.Column.NUMBER_OF_SUB_TASKS + " INTEGER, " 
                + Task.Column.NUMBER_OF_ALL_SUB_TASKS + " INTEGER" 
                + ");");
        
        db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_TASK_CONTACT + " (" 
        		+ "_id INTEGER PRIMARY KEY,"
        		+ "taskId INTEGER,"
                + "contactId INTEGER" 
                + ");");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(TAG, "onUpgrade: " + oldVersion + " - " + newVersion);
        switch (oldVersion) {
        case 10:
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_TASK + " ADD COLUMN " + Task.Column.TYPE + " INTEGER");
        case 11:
            db.execSQL("update " + DatabaseHelper.TABLE_TASK + " set " + Task.Column.TYPE + "=" + TaskStatus.UNPROCESSED.getType()
                            + " where " + Task.Column.TYPE + "= 3");
        case 12:
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_TASK + " ADD COLUMN " + Task.Column.STATUS + " INTEGER");
            db.execSQL("update " + DatabaseHelper.TABLE_TASK + " set " + Task.Column.STATUS + "=" + Task.Column.TYPE);
        case 13:
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_CONTEXT + " ADD COLUMN " + GtdContext.Column.NUMBER_OF_TASKS + " INTEGER");
        case 16:
            db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_TASK_ATTACHMENT + " (" 
                            + "_id INTEGER PRIMARY KEY, "
                            + "taskId INTEGER, "
                            + "type TEXT, "
                            + "url TEXT"
                            + ");");
        case 17:
            try {
                db.execSQL("DROP TABLE " + DatabaseHelper.TABLE_TASK_ATTACHMENT + ";");
            } catch (Exception e) {
            }
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_TASK + " ADD COLUMN " + Task.Column.NUMBER_OF_SUB_TASKS + " INTEGER");
        case 18:
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_TASK + " ADD COLUMN " + Task.Column.NUMBER_OF_ALL_SUB_TASKS + " INTEGER");
        }
        updateNumberOfTasksInAllContexts(db);
        updateNumberOfSubTasksInAllTasks(db);
    }
    
    /**
     * Update the numberOfTasks field in all contexts.
     * @param db Database.
     */
    public void updateNumberOfTasksInAllContexts(SQLiteDatabase db) {
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(DatabaseHelper.TABLE_CONTEXT);
        qb.setProjectionMap(CONTEXT_LIST_PROJECT_MAP);
        Cursor contextCursor = null;
        Cursor taskCursor = null;
        try {
            contextCursor = qb.query(db, new String[] {GtdContext.ID}, null, null, null, null, null);
            for (contextCursor.moveToFirst(); !contextCursor.isAfterLast(); contextCursor.moveToNext()) {
                int contextId = contextCursor.getInt(0);
                SQLiteQueryBuilder taskQuery = new SQLiteQueryBuilder();
                taskQuery.setTables(DatabaseHelper.TABLE_TASK);
                taskQuery.setProjectionMap(TASK_LIST_PROJECT_MAP);
                taskCursor = taskQuery.query(db, new String[] {Task.Column.ID}, Task.Column.CONTEXT_ID + " = ?",
                                new String[] {Integer.toString(contextId)}, null, null, null);
                int numberOfTasks = 0;
                if (taskCursor.moveToFirst()) {
                    numberOfTasks = taskCursor.getCount();
                }
                ContentValues values = new ContentValues();
                values.put(GtdContext.Column.NUMBER_OF_TASKS, numberOfTasks);
                db.update(DatabaseHelper.TABLE_CONTEXT, values, GtdContext.ID + " = ?", new String[] {Integer.toString(contextId)});
                taskCursor.close();
                taskCursor = null;
            }
        } finally {
            if (contextCursor != null) {
                contextCursor.close();
            }
            if (taskCursor != null) {
                taskCursor.close();
            }
        }
    }

    /**
     * Update the numberOfSubTasks field in all tasks.
     * @param db Database.
     */
    public void updateNumberOfSubTasksInAllTasks(SQLiteDatabase db) {
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(DatabaseHelper.TABLE_TASK);
        qb.setProjectionMap(TASK_LIST_PROJECT_MAP);
        Cursor parentTaskCursor = null;
        Cursor taskCursor = null;
        try {
            Map<Integer, Integer> visitedTasks = new HashMap<Integer, Integer>();
            parentTaskCursor = qb.query(db, new String[] {Task.Column.ID}, Task.Column.PARENT_TASK_ID + " is null", null, null, null, null);
            for (parentTaskCursor.moveToFirst(); !parentTaskCursor.isAfterLast(); parentTaskCursor.moveToNext()) {
                int taskId = parentTaskCursor.getInt(0);
                updateNumberOfSubTasksOfTask(db, taskId, visitedTasks);
            }
        } finally {
            if (parentTaskCursor != null) {
                parentTaskCursor.close();
            }
            if (taskCursor != null) {
                taskCursor.close();
            }
        }
    }

    /**
     * Update the numberOfSubTasks field in all tasks.
     * @param db Database.
     * @param taskId Task ID.
     * @return Total number of sub tasks (recursively).
     */
    public int updateNumberOfSubTasksOfTask(SQLiteDatabase db, int taskId,Map<Integer, Integer> visitedTasks) {
        int totalSubTasks = 0;
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(DatabaseHelper.TABLE_TASK);
        qb.setProjectionMap(TASK_LIST_PROJECT_MAP);
        Cursor subTaskCursor = null;
        try {
            subTaskCursor = qb.query(db, new String[] {Task.Column.ID}, Task.Column.PARENT_TASK_ID + " = ?",
                            new String[] {Integer.toString(taskId)}, null, null, null);
            int directSubTasks = 0;
            for (subTaskCursor.moveToFirst(); !subTaskCursor.isAfterLast(); subTaskCursor.moveToNext()) {
                int subTaskId = subTaskCursor.getInt(0);
                directSubTasks++;
                totalSubTasks++;
                if (visitedTasks.containsKey(subTaskId)) {
                    totalSubTasks += visitedTasks.get(subTaskId);
                } else {
                    totalSubTasks += updateNumberOfSubTasksOfTask(db, subTaskId, visitedTasks);
                }
            }
            ContentValues values = new ContentValues();
            values.put(Task.Column.NUMBER_OF_SUB_TASKS, directSubTasks);
            values.put(Task.Column.NUMBER_OF_ALL_SUB_TASKS, directSubTasks);
            db.update(DatabaseHelper.TABLE_TASK, values, Task.Column.ID + " = ?", new String[] {Integer.toString(taskId)});
            visitedTasks.put(taskId, totalSubTasks);
        } finally {
            if (subTaskCursor != null) {
                subTaskCursor.close();
            }
        }
        return totalSubTasks;
    }

    /**
     * Get the total number of sub tasks of task with taskId.
     * @param db Database.
     * @param taskId Task ID.
     */
    public int getTotalNumberOfSubTasksOfTask(SQLiteDatabase db, int taskId) {
        int totalSubTasks = 0;
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(DatabaseHelper.TABLE_TASK);
        qb.setProjectionMap(TASK_LIST_PROJECT_MAP);
        Cursor taskCursor = null;
        try {
            taskCursor = qb.query(db, new String[] {Task.Column.NUMBER_OF_ALL_SUB_TASKS},
                            Task.Column.PARENT_TASK_ID + " = ?",
                            new String[] {Integer.toString(taskId)}, null, null, null);
            if (taskCursor.moveToFirst()) {
                totalSubTasks = taskCursor.getInt(0);
            }
        } finally {
            if (taskCursor != null) {
                taskCursor.close();
            }
        }
        return totalSubTasks;
    }

    static {
        CONTEXT_LIST_PROJECT_MAP = new HashMap<String, String>();
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.ID, DatabaseHelper.TABLE_CONTEXT + "._id");
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.NAME, DatabaseHelper.TABLE_CONTEXT + ".name");
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.COLOUR, DatabaseHelper.TABLE_CONTEXT + ".colour");
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.Column.NUMBER_OF_TASKS, DatabaseHelper.TABLE_CONTEXT + ".numberOfTasks");
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.ICON, DatabaseHelper.TABLE_CONTEXT + ".iconId");
        CONTEXT_LIST_PROJECT_MAP.put(Column.PARENT_CONTEXT_ID, DatabaseHelper.TABLE_CONTEXT + "." + Column.PARENT_CONTEXT_ID);
        
        TASK_LIST_PROJECT_MAP = new HashMap<String, String>();
        TASK_LIST_PROJECT_MAP.put(Task.Column.ID, DatabaseHelper.TABLE_TASK + "._id");
        TASK_LIST_PROJECT_MAP.put(Task.Column.DESCRIPTION, DatabaseHelper.TABLE_TASK + ".description");
        TASK_LIST_PROJECT_MAP.put(Task.Column.DETAILS, DatabaseHelper.TABLE_TASK + ".details");
        TASK_LIST_PROJECT_MAP.put(Task.Column.CONTEXT_ID, DatabaseHelper.TABLE_TASK + ".contextId");
        TASK_LIST_PROJECT_MAP.put(Task.Column.PARENT_TASK_ID, DatabaseHelper.TABLE_TASK + ".parentTaskId");
        TASK_LIST_PROJECT_MAP.put(Task.Column.CREATED_DATE, DatabaseHelper.TABLE_TASK + ".created");
        TASK_LIST_PROJECT_MAP.put(Task.Column.MODIFIED_DATE, DatabaseHelper.TABLE_TASK + ".modified");
        TASK_LIST_PROJECT_MAP.put(Task.Column.START_DATE, DatabaseHelper.TABLE_TASK + ".startDate");
        TASK_LIST_PROJECT_MAP.put(Task.Column.DUE_DATE, DatabaseHelper.TABLE_TASK + ".dueDate");
        TASK_LIST_PROJECT_MAP.put(Task.Column.DISPLAY_ORDER, DatabaseHelper.TABLE_TASK + ".displayOrder");
        TASK_LIST_PROJECT_MAP.put(Task.Column.COMPLETE, DatabaseHelper.TABLE_TASK + ".complete");
        TASK_LIST_PROJECT_MAP.put(Task.Column.TYPE, DatabaseHelper.TABLE_TASK + ".type");
        TASK_LIST_PROJECT_MAP.put(Task.Column.STATUS, DatabaseHelper.TABLE_TASK + ".status");
        TASK_LIST_PROJECT_MAP.put(Task.Column.NUMBER_OF_SUB_TASKS, DatabaseHelper.TABLE_TASK + ".numberOfSubTasks");
        TASK_LIST_PROJECT_MAP.put(Task.Column.NUMBER_OF_ALL_SUB_TASKS, DatabaseHelper.TABLE_TASK + ".numberOfAllSubTasks");
        TASK_LIST_PROJECT_MAP.put(Task.CONTEXT_NAME, DatabaseHelper.TABLE_CONTEXT + ".name");
        TASK_LIST_PROJECT_MAP.put(Task.CONTEXT_COLOUR, DatabaseHelper.TABLE_CONTEXT + ".colour");
        TASK_LIST_PROJECT_MAP.put(Task.CONTEXT_ICON, DatabaseHelper.TABLE_CONTEXT + ".iconId");

        TASK_ATTACHMENT_PROJECT_MAP = new HashMap<String, String>();
        TASK_ATTACHMENT_PROJECT_MAP.put(TaskAttachment.Column.ID, DatabaseHelper.TABLE_TASK_ATTACHMENT + "."
                        + TaskAttachment.Column.ID);
        TASK_ATTACHMENT_PROJECT_MAP.put(TaskAttachment.Column.TASK_ID, DatabaseHelper.TABLE_TASK_ATTACHMENT + "."
                        + TaskAttachment.Column.TASK_ID);
        TASK_ATTACHMENT_PROJECT_MAP.put(TaskAttachment.Column.TYPE, DatabaseHelper.TABLE_TASK_ATTACHMENT + "."
                        + TaskAttachment.Column.TYPE);
        TASK_ATTACHMENT_PROJECT_MAP.put(TaskAttachment.Column.URL, DatabaseHelper.TABLE_TASK_ATTACHMENT + "."
                        + TaskAttachment.Column.URL);
    }
}